﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.OleDb;
using System.IO;
//using Ionic.Zip;

namespace DslamPort.Models
{
    public class DongBoModels
    {

        public static bool dongboadsl(HttpPostedFileBase file)
        {
            string ConStr = "";

            string ext = Path.GetExtension(file.FileName).ToLower();

            string path = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/" + file.FileName);
            file.SaveAs(path);
            if (ext.Trim() == ".xls")
            {
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                return false;
            }
            string query = "SELECT * FROM [Data$]";
            OleDbConnection conn = new OleDbConnection(ConStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            //gvExcelFile.DataSource = ds.Tables[0];
            //gvExcelFile.DataBind();
            bool kq = XuLy(ds);
            conn.Close();
            return kq;

        }
        //MYTV NEW
        public static bool dongboMYTVnew(HttpPostedFileBase file)
        {
            string ConStr = "";

            string ext = Path.GetExtension(file.FileName).ToLower();

            string path = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/" + file.FileName);
            file.SaveAs(path);
            if (ext.Trim() == ".xls")
            {
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                return false;
            }
            string query = "SELECT * FROM [Data$]";
            OleDbConnection conn = new OleDbConnection(ConStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            //gvExcelFile.DataSource = ds.Tables[0];
            //gvExcelFile.DataBind();
            bool kq = XuLy_MYTV(ds);
            conn.Close();
            return kq;

        }
        public static bool XuLy(DataSet ds)
        {
            bool KQ = true;
            DataTable dt = ds.Tables[0];
            string ID;
            string SYSTEMID;
            string RACKID;
            string SELFID;
            string SLOT;
            string PORT;
            string VPI;
            string VCI;
            string ACCOUNT;
            string GOIDV;
            string MA_KH;
            string TEN_KH;
            string TRANG_THAI;
            string IP_TINH;
            string hinh_thuc_int;
            string DIACHI_KH;
            string ngay_dk_int;
            string BRAS_IP;
            string USER_NAME;
            string caulenh = "";
            int num = 0;
            foreach (DataRow row in dt.Rows)
            {

                //===chi co 7 tham so==/
                ID = row[0].ToString();
                if (ID == "")
                {
                    continue;
                }
                if (int.TryParse(ID, out num))
                {
                    ACCOUNT = row[1].ToString();
                    GOIDV = row[2].ToString();
                    MA_KH = row[3].ToString();
                    string s5 = row[4].ToString();//ko
                    TEN_KH = row[5].ToString();
                    TRANG_THAI = row[6].ToString();
                    if (TRANG_THAI == "Hoạt động")
                    {
                        TRANG_THAI = "1";
                    }
                    else if (TRANG_THAI == "Khóa")
                    {
                        TRANG_THAI = "2";
                    }
                    else
                    {
                        TRANG_THAI = "0";
                    }

                    IP_TINH = row[8].ToString();
                    hinh_thuc_int = row[9].ToString();
                    DIACHI_KH = row[10].ToString();
                    ngay_dk_int = row[11].ToString();
                    BRAS_IP = row[12].ToString();
                    string s8 = row[7].ToString();
                    int tam=0;
                    string[] DSLAM = s8.Replace(":-:", "*").Split('*');
                    //kiem tra là mấy tham số
                    if (s8.Length > 50)//7 tham số
                    {



                        SYSTEMID = DSLAM[0].Substring(9);
                        RACKID = DSLAM[1].Substring(7);
                        SELFID = DSLAM[2].Substring(7);
                        tam = int.Parse(DSLAM[3].Substring(5));
                        SLOT = tam.ToString();
                        tam = int.Parse(DSLAM[4].Substring(5));
                        PORT = tam.ToString();
                        VPI = DSLAM[5].Substring(4);
                        VCI = DSLAM[6].Substring(4);
                        caulenh = "UPDATE dslam_port a "
   + "SET a.account_int='" + ACCOUNT + "',a.goidv_int='" + GOIDV + "',a.ma_kh='" + MA_KH + "',a.ten_kh='" + TEN_KH + "',a.trang_thai_int=" + TRANG_THAI + ",a.ip_tinh='" + IP_TINH + "',a.hinh_thuc_int='" + hinh_thuc_int + "',a.diachi_kh='" + DIACHI_KH + "',a.ngay_dk_int=TO_DATE( '" + ngay_dk_int + "', 'dd/mm/yyyy hh24:mi:ss'),a.bras_ip='" + BRAS_IP + "',a.status='1'"
   + "WHERE (a.system_id='" + SYSTEMID + "' and a.slot='" + SLOT + "' and a.port='" + PORT + "' and a.vpi='"+VPI+"')";
                    }
                    else
                    {
                        // SYSTEMID = row[13].ToString();
                        SLOT = DSLAM[0].Substring(5);
                        PORT = DSLAM[1].Substring(5);
                        VPI = DSLAM[2].Substring(4);
                        VCI = DSLAM[3].Substring(4);
                        caulenh = "UPDATE dslam_port a "
+ "SET a.account_int='" + ACCOUNT + "',a.goidv_int='" + GOIDV + "',a.ma_kh='" + MA_KH + "',a.ten_kh='" + TEN_KH + "',a.trang_thai_int=" + TRANG_THAI + ",a.ip_tinh='" + IP_TINH + "',a.hinh_thuc_int='" + hinh_thuc_int + "',a.diachi_kh='" + DIACHI_KH + "',a.ngay_dk_int=TO_DATE( '" + ngay_dk_int + "', 'dd/mm/yyyy hh24:mi:ss'),a.bras_ip='" + BRAS_IP + "',a.status='1'"
+ "WHERE ( a.slot='" + SLOT + "' and a.port='" + PORT + "' and a.vpi='" + VPI + "' and a.vci='" + VCI + "' )";

                    }






                    BaseClass.Lib_ExecuteNonQuery(caulenh);
                }

            }

            return KQ;


        }
        //public static bool dongbomytv(HttpPostedFileBase file)
        //{
        //    //xu ly giai nen
        //    string extractPath = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/MYTV");
        //    //XÓA THƯ MUC 
        //    foreach (string path in Directory.GetFiles(extractPath))
        //    {
        //        System.IO.File.Delete(path);
        //    }

        //    using (ZipFile zip = ZipFile.Read(file.InputStream))
        //    {
        //        zip.ExtractAll(extractPath, ExtractExistingFileAction.DoNotOverwrite);

        //    }
        //    //xu ly dong bo
        //    string ConStr = "";

        //    bool kq = false;
        //    foreach (string path in Directory.GetFiles(extractPath))
        //    {

        //        string ext = ".xlsx";
        //        if (ext.Trim() == ".xls")
        //        {
        //            ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        //        }
        //        else if (ext.Trim() == ".xlsx")
        //        {
        //            ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        //        }
        //        else
        //        {
        //            return false;
        //        }
        //        string query = "SELECT * FROM [Data$]";
        //        OleDbConnection conn = new OleDbConnection(ConStr);
        //        if (conn.State == ConnectionState.Closed)
        //        {
        //            conn.Open();
        //        }
        //        OleDbCommand cmd = new OleDbCommand(query, conn);
        //        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        //        DataSet ds = new DataSet();
        //        da.Fill(ds);
        //        if (!XuLy_MYTV(ds))
        //        {
        //            return false;
        //        }
        //        conn.Close();
        //    }
        //    return true;
        //}
        public static bool XuLy_MYTV(DataSet ds)
        {
            bool KQ = true;
            DataTable dt = ds.Tables[0];

            string SYSTEMID = "";
            string SLOT;
            string PORT;
            string VPI="0";
            string ACCOUNTMYTV;
            string TEN_KH;
            string TRANG_THAIMYTV;
            string DIACHI_KH;
            string goidv_mytv;
            int dem = 0;
            string caulenh = "";
            bool gpon_check=false;
            try
            {
            foreach (DataRow row in dt.Rows)
            {

                SYSTEMID = row[5].ToString();
                if (SYSTEMID == "Thiết bị" || SYSTEMID =="" )
                {
                    continue;
                }
                string s = row[8].ToString();
                string[] thongso = s.Split('/');
                SLOT = thongso[1].ToString();
               

                if (thongso[2].ToString().IndexOf(":")!=-1)
                {
                    //la gpon
                    gpon_check = true;
                    string[] thongso2 = thongso[2].ToString().Split(':');
                    PORT = thongso2[0].ToString();
                    VPI = thongso2[1].ToString();
                        
                }
                else{
                    PORT = thongso[2].ToString();
                }

                ACCOUNTMYTV = row[0].ToString();
                TEN_KH = row[2].ToString();

                TRANG_THAIMYTV = "1";
                DIACHI_KH = row[3].ToString();
                goidv_mytv = row[4].ToString();
                if (gpon_check)
                {
                    caulenh = "UPDATE dslam_port a "
+ "SET  a.account_mytv='" + ACCOUNTMYTV + "',a.trang_thai_mytv='" + TRANG_THAIMYTV + "',a.goidv_mytv='" + goidv_mytv + "',a.ten_kh='" + TEN_KH + "',a.diachi_kh='" + DIACHI_KH + "',a.status=1"
+ " WHERE (a.system_id like '%" + SYSTEMID + "%' and a.slot='" + SLOT + "' and a.port='" + PORT + "'and a.vpi='" + VPI + "' )";
                }
                else{
                caulenh = "UPDATE dslam_port a "
+ "SET  a.account_mytv='" + ACCOUNTMYTV + "',a.trang_thai_mytv='" + TRANG_THAIMYTV + "',a.goidv_mytv='" + goidv_mytv + "',a.ten_kh='" + TEN_KH + "',a.diachi_kh='" + DIACHI_KH + "',a.status=1"
+ " WHERE (a.system_id like '%" + SYSTEMID + "%' and a.slot='" + SLOT + "' and a.port='" + PORT + "' )";
                //and a.vpi='" + VPI + "'
                }
                BaseClass.Lib_ExecuteNonQuery(caulenh);
            }
            }
            catch (Exception)
            {

                return false;
            }

            return KQ;
        }
        public static bool dongbodslam(HttpPostedFileBase file)
        {
            string ConStr = "";

            string ext = Path.GetExtension(file.FileName).ToLower();

            string path = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/" + file.FileName);


            file.SaveAs(path);
            // Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";

            if (ext.Trim() == ".xls")
            {
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                return false;
            }
            string query = "SELECT * FROM [Data$]";
            OleDbConnection conn = new OleDbConnection(ConStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            //gvExcelFile.DataSource = ds.Tables[0];
            //gvExcelFile.DataBind();
            bool kq = XuLydslam(ds);
            conn.Close();
            return kq;

        }
        public static bool XuLydslam(DataSet ds)
        {
            bool KQ = true;
            DataTable dt = ds.Tables[0];
            DslamModel dslam = new DslamModel();

            try
            {



                foreach (DataRow row in dt.Rows)
                {
                    dslam.system_id = row[0].ToString();
                    dslam.name = row[1].ToString();
                    if (dslam.name == "")
                    {
                        continue;
                    }
                    dslam.dslam_type_id = row[2].ToString();
                    dslam.quan_huyen_id = row[3].ToString();
                    dslam.slot_dslam = row[4].ToString();
                    dslam.ip = row[5].ToString();
                    dslam.ttlienhe = row[8].ToString();
                    dslam.ghichu = row[9].ToString();
                    dslam.status = "1";
                    dslam.xa_phuong_id = row[6].ToString();
                    dslam.todanpho_id = row[7].ToString();
                    dslam.Vlan = row[10].ToString();
                    if (string.IsNullOrEmpty(dslam.xa_phuong_id))
                    {
                        dslam.xa_phuong_id = "0";
                    }
                    if (string.IsNullOrEmpty(dslam.todanpho_id))
                    {
                        dslam.todanpho_id = "0";
                    }
                    if (DslamModel.checkExist(dslam) <= 0)
                    {
                        DslamModel.addDslam(dslam,1);
                    }
                }
            }
            catch (Exception)
            {

                KQ = false;
            }
            return KQ;


        }
        //public bool giainen(HttpPostedFileBase file)
        //{
        //    string extractPath = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/");

        //    using (ZipFile zip = ZipFile.Read(file.InputStream))
        //    {
        //        zip.ExtractAll(extractPath, ExtractExistingFileAction.DoNotOverwrite);

        //    }
        //    return true;
        //}
        internal static bool dongbodslam4(HttpPostedFileBase file)
        {
            string ConStr = "";

            string ext = Path.GetExtension(file.FileName).ToLower();

            string path = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/" + file.FileName);


            file.SaveAs(path);

            if (ext.Trim() == ".xls")
            {
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                return false;
            }
            string query = "SELECT * FROM [Data$]";
            OleDbConnection conn = new OleDbConnection(ConStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            //gvExcelFile.DataSource = ds.Tables[0];
            //gvExcelFile.DataBind();
            bool kq = XuLy4(ds);
            conn.Close();
            return kq;
        }
        private static bool XuLy4(DataSet ds)
        {
            bool KQ = true;
            DataTable dt = ds.Tables[0];
            string SLOT;
            string PORT;
            string VPI;
            string VCI;
            string ACCOUNT;
            string GOIDV;
            string TEN_KH;
            string DIACHI_KH;
            string SYSTEMID;
            string caulenh;
            foreach (DataRow row in dt.Rows)
            {
                string s = row[0].ToString();

                string[] DSLAM = s.Trim().Split('/');
                SLOT = DSLAM[0];
                PORT = DSLAM[1];
                VPI = DSLAM[2];
                VCI = DSLAM[3];
                DIACHI_KH = row[4].ToString();
                ACCOUNT = row[2].ToString();
                GOIDV = row[1].ToString();
                TEN_KH = row[3].ToString();
                SYSTEMID = row[5].ToString();
                caulenh = "UPDATE dslam_port a SET a.goidv_int='" + GOIDV + "',a.account_int='" + ACCOUNT + "',a.ten_kh='" + TEN_KH + "',a.diachi_kh='" + DIACHI_KH + "',a.status=1 WHERE a.system_id='" + SYSTEMID + "' and a.vpi='" + VPI + "' and a.vci='" + VCI + "'";
                BaseClass.Lib_ExecuteNonQuery(caulenh);
            }

            return KQ;
        }

        //public static bool GPON(HttpPostedFileBase file)
        //{
        //    string ConStr = "";

        //    string ext = Path.GetExtension(file.FileName).ToLower();

        //    string path = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/" + file.FileName);


        //    file.SaveAs(path);
        //    // Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";

        //    if (ext.Trim() == ".xls")
        //    {
        //        ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        //    }
        //    else if (ext.Trim() == ".xlsx")
        //    {
        //        ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //    string query = "SELECT * FROM [Data$]";
        //    OleDbConnection conn = new OleDbConnection(ConStr);
        //    if (conn.State == ConnectionState.Closed)
        //    {
        //        conn.Open();
        //    }
        //    OleDbCommand cmd = new OleDbCommand(query, conn);
        //    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        //    DataSet ds = new DataSet();
        //    da.Fill(ds);
        //    //gvExcelFile.DataSource = ds.Tables[0];
        //    //gvExcelFile.DataBind();
        //    bool kq = XuLyGPON(ds);
        //    conn.Close();
        //    return kq;


        //}

        //public static bool XuLyGPON(DataSet ds)
        //{
        //    bool KQ = true;
        //    DataTable dt = ds.Tables[0];
        //    GPONModel dslam = new GPONModel();

        //    try
        //    {



        //        foreach (DataRow row in dt.Rows)
        //        {
        //            dslam.system_id = row[0].ToString();
        //            dslam.name = row[1].ToString();
        //            if (dslam.name == "")
        //            {
        //                continue;
        //            }
        //            dslam.dslam_type_id = row[2].ToString();
        //            dslam.quan_huyen_id = row[3].ToString();
        //            dslam.slot_dslam = row[4].ToString();
        //            dslam.ip = row[5].ToString();
        //            dslam.ttlienhe = row[8].ToString();
        //            dslam.ghichu = row[9].ToString();
        //            dslam.status = "1";
        //            dslam.xa_phuong_id = row[6].ToString();
        //            dslam.todanpho_id = row[7].ToString();
        //            dslam.Vlan = row[10].ToString();
        //            if (string.IsNullOrEmpty(dslam.xa_phuong_id))
        //            {
        //                dslam.xa_phuong_id = "0";
        //            }
        //            if (string.IsNullOrEmpty(dslam.todanpho_id))
        //            {
        //                dslam.todanpho_id = "0";
        //            }
        //            if (GPONModel.checkExist(dslam) <= 0)
        //            {
        //                GPONModel.addDslam(dslam, 3);
        //            }
        //            else return false;
        //        }
        //    }
        //    catch (Exception)
        //    {

        //        KQ = false;
        //    }
        //    return KQ;

        //}

        public static bool xoa()
        {
          try
          {
              string caulenh = "UPDATE dslam_port a set a.account_int='',a.goidv_int='',a.account_mytv='',a.goidv_mytv='',a.ma_kh='',a.ten_kh='',a.ip_tinh='',a.hinh_thuc_int='',a.diachi_kh='',a.ngay_dk_int='',a.bras_ip='',a.status='0',a.trang_thai_int='',a.trang_thai_mytv=''";
                    BaseClass.Lib_ExecuteNonQuery(caulenh);
              return true;
          }
          catch (Exception)
          {
          	   return false;
          }
        }
    }
}